Product: Microsoft Office Professional Plus 2010

I have created a template file with a data table, 2 pivot tables, and each of those 2 pivot tables has a pivotchart and 2 slicers.  I have some code that updates the pivot table ranges.  When that occurs the pivot tables, pivotcharts and slicers all update successfully.  

The problem comes after saving the .xlsm file.  Upon opening the file I receive a message saying "Excel found unreadable content in 'myTemplate.xlsm'. Do you want to recover the contents of this workbook? If you trus the source of this workbook, click Yes."

I then get a list or removed records and the repair that was done.   

Removed Records: Slicer Cache from /xl/slicerCaches/slicerCache3.xml part (Slicer Cache)
Removed Records: Slicer Cache from /xl/slicerCaches/slicerCache4.xml part (Slicer Cache)
Removed Records: Slicers from /xl/slicers/slicer2.xml part (Slicer)
Removed Records: Drawing from /xl/drawings/drawing4.xml part (Drawing shape)
Repaired Records: Named range from /xl/workbook.xml part (Workbook)

Most of the file is OK with the exception of my last worksheet.  The pivot table and chart get updated, but the slicers are gone.  Any idea why this is happening?  Can something be done to prevent this?

Thanks,

Rich

Hi Rich,

Based on your description, my understanding is that slicers are removed after you get the error messages and repair it. You wonder to know why slicers are lost after repairing, it seems that the corrupted file caused the issue.

Please try to update the  pivot table ranges manually without macros. According to my test, the slicers wont be lost without macros. Please test this method in your own environment. If it works fine after disable macros, please check your code.

If my understanding is incorrect, could you upload a sample via OneDrive and be at bit more precise explain your problem, so that we can get more accurate solutions to this problem. I am glad to help and forward to your reply.

Hope its helpful.

Regards,

• Edited by guGuuuMy 6 hours 5 minutes ago